package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Function;
import com.hnl.util.FormatUtil;

public class DBFunctionDAO implements FunctionDAO {
	public static final Logger LOG = LoggerFactory
			.getLogger(DBFunctionDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadByUserIDStmt;
	private PreparedStatement updateStmt;
	private PreparedStatement delStmt;

	protected DBFunctionDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public Function saveRoleFunction(Function function) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO FUNCTION (function, create_date, create_user, modify_date, modify_user) "
									+ "VALUES (?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setInt(1, function.getFunction());
			saveStmt.setString(2,
					FormatUtil.getXmlDate(function.getCreateDate()));
			saveStmt.setInt(3,
					function.getCreateUserID() == null ? Integer.valueOf(1)
							: function.getCreateUserID());
			saveStmt.setString(4,
					FormatUtil.getXmlDate(function.getModifyDate()));
			saveStmt.setInt(5,
					function.getModifyUserID() == null ? Integer.valueOf(1)
							: function.getModifyUserID());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				function.setFunctionID(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving Function.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return function;
	}

	public Collection<Function> loadFunctionsByRoleID(Integer roleID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<Function> roleFunctionCollection = new Vector<Function>();
		try {
			loadByUserIDStmt = conn
					.prepareStatement("SELECT function.function_ID, function.function, function.create_date, function.create_user, function.modify_date, function.modify_user"
							+ " FROM role_function, role, function"
							+ " WHERE role_function.role_ID = ?"
							+ " AND role_function.function_ID = function.function_ID");
			loadByUserIDStmt.setInt(1, roleID);
			ResultSet rs = loadByUserIDStmt.executeQuery();
			while (rs.next()) {
				Function function = new Function();
				function.setFunctionID(rs.getInt("function_ID"));
				function.setFunction(rs.getInt("function"));
				function.setCreateDate(rs.getDate("create_date"));
				function.setCreateUserID(rs.getInt("create_user"));
				function.setModifyDate(rs.getDate("modify_date"));
				function.setModifyUserID(rs.getInt("modify_user"));
				roleFunctionCollection.add(function);
			}
			rs.close();
			loadByUserIDStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Function.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return roleFunctionCollection;

	}

	public void updateFunction(Function function) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			updateStmt = conn
					.prepareStatement("UPDATE FUNCTION SET function=?, create_date=?, create_user=?, modify_date=?, modify_user=?"
							+ "WHERE function_ID=?");
			updateStmt.setInt(1, function.getFunction());
			updateStmt.setString(2,
					FormatUtil.getXmlDate(function.getCreateDate()));
			updateStmt.setInt(3,
					function.getCreateUserID() == null ? Integer.valueOf(1)
							: function.getCreateUserID());
			updateStmt.setString(4,
					FormatUtil.getXmlDate(function.getModifyDate()));
			updateStmt.setInt(5,
					function.getModifyUserID() == null ? Integer.valueOf(1)
							: function.getModifyUserID());

			updateStmt.setInt(48, function.getFunctionID());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error updating Function.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public void deleteFunction(Integer functionID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			delStmt = conn.prepareStatement("DELETE FROM FUNCTION "
					+ "WHERE function_ID = ?");
			delStmt.setInt(1, functionID);
			delStmt.executeUpdate();
			delStmt.close();
		} catch (SQLException e) {
			LOG.error("Error deleting Function.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

}
